import pandas as pd


def calculate_correlations():
    """
    计算Excel数据中成本与销量、成本与售价、销量与售价之间的相关性，并进行1-sigma异常值处理
    Args:
        file_path (str): Excel文件路径
    Returns:
        tuple: 包含3个字典的元组，分别表示成本与销量的关系、成本与售价的关系、销量与售价的关系
    """
    # 创建字典来存储每个作物的成本、销量和售价关系
    cost_relationships = {}
    sales_relationships = {}
    price_relationships = {}

    # 读取2015到2024年的数据
    for year in range(2015, 2025):
        # 读取对应年份的sheet
        df = pd.read_excel("data/15-24.xlsx", sheet_name=str(year))

        # 确保数据格式正确，转换为数值型，忽略非数字值
        df["种植成本/(元/亩)"] = pd.to_numeric(df["种植成本/(元/亩)"], errors="coerce")
        df["预销售量"] = pd.to_numeric(df["预销售量"], errors="coerce")
        df["销售单价/(元/斤)"] = pd.to_numeric(df["销售单价/(元/斤)"], errors="coerce")

        # 对每一行数据（每种作物）进行处理
        for _, row in df.iterrows():
            crop_id = row["作物编号"]

            # 初始化字典结构
            if crop_id not in cost_relationships:
                cost_relationships[crop_id] = {"cost": [], "sales": [], "price": []}
                sales_relationships[crop_id] = {"sales": []}
                price_relationships[crop_id] = {"price": []}

            # 存储每个作物的种植成本、预销售量和销售单价
            cost_relationships[crop_id]["cost"].append(row["种植成本/(元/亩)"])
            cost_relationships[crop_id]["sales"].append(row["预销售量"])
            cost_relationships[crop_id]["price"].append(row["销售单价/(元/斤)"])

            # 分别存储单独的关系数据
            sales_relationships[crop_id]["sales"].append(row["预销售量"])
            price_relationships[crop_id]["price"].append(row["销售单价/(元/斤)"])

    # 定义一个函数来进行异常值处理（1-sigma）
    def handle_outliers(data_dict):
        """
        对数据字典中的值进行1-sigma异常值处理
        """
        # 提取字典中的值并转换为Series
        values = pd.Series(list(data_dict.values())).dropna()
        mean = values.mean()  # 计算均值
        std = values.std()  # 计算标准差
        upper_limit = mean + std  # +1-sigma
        lower_limit = mean - std  # -1-sigma

        # 使用1-sigma原则替换异常值
        handled_data = {
            k: min(max(v, lower_limit), upper_limit) for k, v in data_dict.items()
        }
        return handled_data

    # 计算成本与销量之间的关系
    cost_sales_correlation = {}
    for x in cost_relationships:
        for y in cost_relationships:
            if x != y:
                # 使用有效数据进行相关系数计算
                cost_x = pd.Series(cost_relationships[x]["cost"]).dropna()
                sales_y = pd.Series(cost_relationships[y]["sales"]).dropna()
                if len(cost_x) > 1 and len(sales_y) > 1:
                    correlation = cost_x.corr(sales_y)
                    cost_sales_correlation[(x, y)] = correlation

    # 计算成本与售价之间的关系
    cost_price_correlation = {}
    for x in cost_relationships:
        for y in cost_relationships:
            if x != y:
                cost_x = pd.Series(cost_relationships[x]["cost"]).dropna()
                price_y = pd.Series(cost_relationships[y]["price"]).dropna()
                if len(cost_x) > 1 and len(price_y) > 1:
                    correlation = cost_x.corr(price_y)
                    cost_price_correlation[(x, y)] = correlation

    # 计算销量与售价之间的关系
    sales_price_correlation = {}
    for x in sales_relationships:
        for y in sales_relationships:
            if x != y:
                sales_x = pd.Series(sales_relationships[x]["sales"]).dropna()
                price_y = pd.Series(price_relationships[y]["price"]).dropna()
                if len(sales_x) > 1 and len(price_y) > 1:
                    correlation = sales_x.corr(price_y)
                    sales_price_correlation[(x, y)] = correlation

    # 对求解得到的三组相关系数字典进行1-sigma异常值处理
    cost_sales_correlation = handle_outliers(cost_sales_correlation)
    cost_price_correlation = handle_outliers(cost_price_correlation)
    sales_price_correlation = handle_outliers(sales_price_correlation)
    print(cost_price_correlation)
    # 返回3个相关系数字典
    return cost_sales_correlation, cost_price_correlation, sales_price_correlation


calculate_correlations()
